﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using System.Text;


namespace QQZiFramework.DataBase.SqlClient
{
    class SqlAccess : IDBAccess
    {
        public SqlAccess(DBContext context)
            : base(context)
        {

        }
        public override void Create(string connectionString)
        {
            conStr = connectionString;
        }
        public override void BeginTransaction()
        {
            OpenSql();
            if (trans != null)
            {
                throw new Exception("有未提交的事务，无法再创建！");
            }
            trans = conn.BeginTransaction();
            mContext.LogSqlMessage("DataBase BeginTransaction", true);
        }
        public override DataTable QueryTable(string sql)
        {
            return QueryTable(sql, CommandType.Text);
        }
        public override IDataReader QueryReader(string sql, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            if (reader != null && !reader.IsClosed) throw new ClientException("有已打开的Reader，请先调用reader.close方法关闭");
            var cmd = CreateCommand(sql, cmdType, parameters, true);
            reader = cmd.ExecuteReader();
            cmd.Parameters.Clear();
            cmd = null;
            return reader;
        }
        string lastSql;
        SqlConnection conn;
        SqlTransaction trans;

        SqlDataReader reader;
        string conStr;

        private void OpenSql()
        {
            if (conn == null) conn = new SqlConnection(conStr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
                mContext.LogSqlMessage("******** DataBase Open (" + System.Threading.Thread.CurrentThread.ManagedThreadId + ") ********", true);
            }
        }
        public override IDbConnection GetConnection()
        {
            return conn;
        }
        private SqlCommand CreateCommand(string sql, CommandType cmdType, System.Data.IDbDataParameter[] parameters, bool query)
        {
            OpenSql();
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (conn.ConnectionTimeout > 60) cmd.CommandTimeout = conn.ConnectionTimeout;
            cmd.CommandText = sql;
            cmd.CommandType = cmdType;
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
                sql += "(";
                foreach (SqlParameter parameter in parameters)
                {
                    sql += parameter.ParameterName + "=";
                    if (parameter.Value != null && parameter.Value.ToString().Length > 255) sql += "长内容";
                    else sql += parameter.Value;
                    sql += ",";
                }
                sql = sql.Substring(0, sql.Length - 1) + ")";
            }
            if (trans != null) cmd.Transaction = trans;
            lastSql = sql;
            mContext.LogSqlMessage(sql, query);
            return cmd;
        }

        public override int ExecuteCommand(string sql)
        {
            // 枚举 CommandType.text =1 , （sql语句） CommandType.StoredProcedure = 4,    （存储过程的名称）   CommandType.TableDirect = 512（表名）,
            return ExecuteCommand(sql, CommandType.Text);
        }

        public override object ExecScalar(string sql)
        {
            return ExecScalar(sql, CommandType.Text);
        }

        public override IDbDataParameter CreateParameter(string name, object value)
        {
            return new SqlParameter("@" + name, value);
        }

        /// <summary>
        /// 是否有自增功能
        /// </summary>
        /// <value></value>
        public override bool AotoIdentity
        {
            get { return true; }
        }

        public override string GetNewIdentitySql(string tableName)
        {
            return null;
        }

        public override string GetInsertedIdentitySql(string tableName)
        {
            return "select @@identity";
        }

        public override void Dispose()
        {
            if (conn != null)
            {
                if (conn.State == ConnectionState.Open)
                {
                    if (trans != null)
                    {
                        trans.Rollback();
                        mContext.LogSqlMessage("Rollback", true);
                    }
                    if (reader != null && !reader.IsClosed) reader.Close();
                    conn.Close();
                    mContext.LogSqlMessage("-------- DataBase Close (" + System.Threading.Thread.CurrentThread.ManagedThreadId + ") --------", true);
                }
                conn.Dispose();
                conn = null;
                trans = null;
            }
        }

        /// <summary>
        /// 是否存在表
        /// </summary>
        public override bool IsTableExists(string tableName)
        {
            //是否存在配置表
            object scalar = ExecScalar("select case when  object_id('" + tableName + "') is null then 0 else 1 end");
            if (0.Equals(scalar)) return false;
            else return true;
        }

        /// <summary>
        /// 是否存在列
        /// </summary>
        public override bool IsColumnExists(string tableName, string columnName)
        {
            //是否存在配置表
            object scalar = ExecScalar(" SELECT TOP 1 1 FROM    INFORMATION_SCHEMA.COLUMNS WHERE   [TABLE_NAME] = '" + tableName + "' AND [COLUMN_NAME] = '" + columnName + "'  ");
            if (1.Equals(scalar)) return true;
            else return false;
        }

        /// <summary>
        /// 获取表的主键
        /// </summary>

        public override string[] GetTablePrimaryKeys(string tableName)
        {
            var lst = new List<string>();
            string sql = string.Format("select top 1 COL_NAME(object_id('{0}'),c.colid)	from sysobjects a,sysindexes b,sysindexkeys c	where a.name=b.name and b.id=c.id and b.indid=c.indid and a.xtype='PK' and a.parent_obj=object_id('{0}') 	and c.id=object_id('{0}')", tableName);
            using (IDataReader reader = QueryReader(sql, CommandType.Text, null))
            {
                while (reader.Read())
                {
                    lst.Add(reader[0].ToString());
                }
                reader.Close();
            }
            return lst.ToArray();
        }


        public override string GetLastSql()
        {
            return lastSql;
        }

        public override void Commit()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (trans != null)
                {
                    trans.Commit();
                    trans = null;
                    mContext.LogSqlMessage("DataBase Commit", true);
                }
            }
        }
        public override void Rollback()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans = null;
                    mContext.LogSqlMessage("DataBase Rollback", true);
                }
            }
        }
        public override void Close()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (reader != null && !reader.IsClosed) reader.Close();
                if (trans != null)
                {
                    trans.Commit();
                    trans = null;
                    mContext.LogSqlMessage("DataBase Commit", true);
                }
            }
            Dispose();
        }


        public override String PartQuerySql(string select, string table, string where, string orderby, int start, int end)
        {
            if (orderby == null) throw new ArgumentNullException("pagger query order by");
            string sql;
            if (start == 1 && false)
            {
                //第一页的查询使用top (这里不应用，因为row_number跟order排序出来不一样，会照成第一页的数据不准)
                sql = "select top " + (end - start + 1) + "  " + select + " from " + table;
                if (!string.IsNullOrEmpty(where)) sql += " where " + where;
                sql += " order by " + orderby;
            }
            else
            {
                sql = "select * from (select row_number() over(order by " + orderby + ") rownum, " + select + " from " + table;
                if (!string.IsNullOrEmpty(where)) sql += " where " + where;
                sql += ") t where rownum between " + start + " and " + end;
            }
            return sql;
        }

        // public override String PartQuerySql(string select, string table, string where, string orderby, IDbDataParameter pageIndex, IDbDataParameter pageSize){
        //     if (orderby == null) throw new ArgumentNullException("pagger query order by");
        //     string sql = "select * from (select row_number() over(order by " + orderby + ") rownum, " + select + " from " + table;
        //         if (!string.IsNullOrEmpty(where)) sql += " where " + where;
        //         sql += ") t where rownum between (("+pageIndex.ParameterName+" - 1) * "+ pageSize.ParameterName +" + 1) and "+pageIndex.ParameterName+" * "+ pageSize.ParameterName;
        //     return sql;
        // }

        public override DataTable QueryTable(string text, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, true);
            cmd.CommandType = cmdType;
            System.Data.DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            cmd.Parameters.Clear();
            cmd = null;
            return dt;
        }

        public override int ExecuteCommand(string text, CommandType cmdType, params IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, false);
            cmd.CommandType = cmdType;
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            cmd = null;
            return val;
        }

        public override object ExecScalar(string text, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, text.IndexOf("insert", StringComparison.OrdinalIgnoreCase) == -1);
            object obj = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            cmd = null;
            return obj;
        }

        public override string GetDateConverter(int length)
        {
            return "Convert(varchar(" + length + "),{0},120)";
        }

        public override void CreateTreeExtendTableIfNotExists(string treeTableName)
        {
            ExecuteCommand("if not exists ( select  id from sysobjects where name = 'A0_UserAgencys_REX' and type = 'U' ) Create TABLE " + treeTableName + "_REX( NodeId int  not null,     Deep int not null,     SubNodeId int not Null)");
        }

        public override void BulkInsert(string tableName, DataTable dataTable)
        {
            OpenSql();
            using (var bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.FireTriggers, trans))
            {
                foreach (DataColumn dcPrepped in dataTable.Columns)
                {
                    bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);
                }

                bulkCopy.BulkCopyTimeout = 660;
                bulkCopy.DestinationTableName = tableName;
                bulkCopy.WriteToServer(dataTable);
            }
        }

        /// <summary>
        /// 获取属性的类型转为数据库中定义的类型
        /// </summary>
        public override string ConvertDataType(Type propertyType, bool withSize)
        {

            switch (propertyType.Name)
            {
                case "Int32":
                case "Int64":
                    return "int"; ;
                case "Single":
                case "Double":
                    return "float";
                case "Decimal":
                    if (withSize) return "decimal(18,2)";
                    else return "decimal";
                case "DateTime":
                    return "datetime";
                default:
                    if (withSize) return "varchar(255)";
                    else return "varchar";
            }
        }

        /// <summary>
        /// 创建临时表（通常是根据DataTable)
        /// </summary>
        public override string CreateTempTable(string tableName, DataColumn[] allColumns, DataColumn createPrimaryKey)
        {
            if (allColumns.Length == 0) throw new ArgumentNullException("allColumns");
            string tmpName = "#" + tableName + DateTime.Now.ToString("dd");
            string sql = "CREATE TABLE " + tmpName + " (";
            string columnSql = "";
            foreach (var dc in allColumns)
            {
                columnSql += "," + dc.ColumnName + " " + ConvertDataType(dc.DataType, true);
                if (dc == createPrimaryKey) columnSql += " primary key";
            }
            sql += columnSql.Substring(1) + ")";
            ExecuteCommand(sql);
            return tmpName;
        }

        /// <summary>
        /// 根据临时表的内容，更新物理表
        /// <param name="primaryKey">主键名</param>
        /// <param name="updateColumns">要更新的列</param>
        /// </summary>
        public override void UpdateByTempTable(string tableName, string tempTableFullName, DataColumn[] updateColumns, DataColumn primaryKeyColumn)
        {
            var primaryKey = primaryKeyColumn.ColumnName;
            string sql = "update a set a." + updateColumns[0].ColumnName + "=b." + updateColumns[0].ColumnName;
            for (int i = 1; i < updateColumns.Length; i++)
            {
                sql += ",a." + updateColumns[i].ColumnName + "=b." + updateColumns[i].ColumnName;
            }
            sql += " from " + tableName + " a  join " + tempTableFullName + " b on a." + primaryKey + " = b." + primaryKey;
            ExecuteCommand(sql);
        }

        /// <summary>
        /// 删除临时表
        /// </summary>
        public override void DropTempTable(string tableName)
        {
            ExecuteCommand("drop table " + tableName);
        }

        public override Dictionary<string, string> GetTables()
        {
            var dt = QueryTable(@"select Name,Comment from (
select a.Name ,CONVERT(NVARCHAR(100),g.[value]) AS Comment ,ROW_NUMBER() over (PARTITION by a.name order by  g.name  asc) row_index
from sys.tables a 
left join sys.extended_properties g on (a.object_id = g.major_id AND g.minor_id = 0) and  g.name  like '%COMMENT%'
) t where row_index =1");
            var result = new Dictionary<string, string>();
            foreach (DataRow row in dt.Rows)
            {
                string comment = null;
                if (row["Comment"] != DBNull.Value) comment = row["Comment"].ToString();
                result.Add((string)row["Name"], comment);
            }
            return result;
        }

        public override ForeignKeyItem[] GetTableForeignKeys(string tableName)
        {
            var dt = QueryTable(@"SELECT Field=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid) ,
FKTable=object_name(b.rkeyid),
FKKeyField=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
FROM sysobjects a
join sysforeignkeys b on a.id=b.constid
join sysobjects c on a.parent_obj=c.id
where a.xtype='f' AND c.xtype='U'and object_name(b.fkeyid)='" + tableName + "' ");
            ForeignKeyItem[] result = new ForeignKeyItem[dt.Rows.Count];
            for (var i = 0; i < result.Length; i++)
            {
                var row = dt.Rows[i];
                result[i] = new ForeignKeyItem { ColumnName = row["Field"].ToString(), ForeignTableName = row["FKTable"].ToString(), ForeignColumnName = row["FKKeyField"].ToString() };
            }
            return result;
        }

        static Dictionary<string, string> functionFormat = new Dictionary<string, string>();

        static SqlAccess()
        {
            functionFormat.Add("date", "convert(varchar(10),{0},120)");
            functionFormat.Add("datetime", "convert(varchar(20),{0},120)");
            functionFormat.Add("isnull", "isnull({0},{1})");
            functionFormat.Add("to_date", "select convert (date,{0})");
            functionFormat.Add("to_datetime", "select convert (datetime,{0})");
        }

        public override Dictionary<string, string> FunctionFormatMap
        {
            get { return functionFormat; }
        }
    }
}
